from flask import Flask, render_template, request, jsonify, send_file, redirect, url_for
import os
import sqlite3
from werkzeug.utils import secure_filename
import json
from datetime import datetime

app = Flask(__name__)
app.config['UPLOAD_FOLDER'] = 'static/uploads'
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024 * 1024  # 16GB 无限制上传
app.config['DATABASE'] = 'instance/music_platform.db'

# 确保上传目录存在
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)
os.makedirs('instance', exist_ok=True)

def init_db():
    """初始化数据库"""
    conn = sqlite3.connect(app.config['DATABASE'])
    cursor = conn.cursor()
    
    # 创建资源表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS resources (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            filename TEXT NOT NULL,
            original_name TEXT NOT NULL,
            file_type TEXT NOT NULL,  -- 'ppt' 或 'lesson_plan'
            grade TEXT NOT NULL,      -- 年级
            semester TEXT NOT NULL,   -- 学期
            upload_time DATETIME DEFAULT CURRENT_TIMESTAMP,
            uploader TEXT NOT NULL
        )
    ''')
    
    # 创建课表表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS schedules (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            teacher_name TEXT NOT NULL,
            schedule_file TEXT NOT NULL,
            file_type TEXT NOT NULL,  -- 'image' 或 'table'
            upload_time DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    conn.commit()
    conn.close()

@app.route('/')
def index():
    """首页"""
    return render_template('index.html')

@app.route('/upload')
def upload_page():
    """上传页面"""
    return render_template('upload.html')

@app.route('/resources')
def resources_page():
    """资源列表页面"""
    return render_template('resources.html')

@app.route('/schedules')
def schedules_page():
    """课表页面"""
    return render_template('schedules.html')

@app.route('/api/upload', methods=['POST'])
def upload_file():
    """文件上传API"""
    try:
        file = request.files['file']
        file_type = request.form['file_type']  # 'ppt' 或 'lesson_plan'
        grade = request.form['grade']
        semester = request.form['semester']
        uploader = request.form['uploader']
        
        if file:
            filename = secure_filename(file.filename)
            timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            new_filename = f"{timestamp}_{filename}"
            
            # 保存文件
            subfolder = 'ppt' if file_type == 'ppt' else 'lesson_plan'
            file_path = os.path.join(app.config['UPLOAD_FOLDER'], subfolder, new_filename)
            os.makedirs(os.path.dirname(file_path), exist_ok=True)
            file.save(file_path)
            
            # 保存到数据库
            conn = sqlite3.connect(app.config['DATABASE'])
            cursor = conn.cursor()
            cursor.execute('''
                INSERT INTO resources (filename, original_name, file_type, grade, semester, uploader)
                VALUES (?, ?, ?, ?, ?, ?)
            ''', (new_filename, filename, file_type, grade, semester, uploader))
            conn.commit()
            conn.close()
            
            return jsonify({'success': True, 'message': '文件上传成功'})
    
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)})

@app.route('/api/upload_schedule', methods=['POST'])
def upload_schedule():
    """课表上传API"""
    try:
        file = request.files.get('file')
        table_content = request.form.get('table_content')
        teacher_name = request.form['teacher_name']
        
        if file:
            filename = secure_filename(file.filename)
            timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            new_filename = f"{timestamp}_{filename}"
            
            file_path = os.path.join(app.config['UPLOAD_FOLDER'], 'schedule', new_filename)
            os.makedirs(os.path.dirname(file_path), exist_ok=True)
            file.save(file_path)
            
            file_type = 'image'
            schedule_file = new_filename
        elif table_content:
            # 保存表格内容为HTML文件
            filename = f"{teacher_name}_schedule_{datetime.now().strftime('%Y%m%d_%H%M%S')}.html"
            file_path = os.path.join(app.config['UPLOAD_FOLDER'], 'schedule', filename)
            os.makedirs(os.path.dirname(file_path), exist_ok=True)
            
            with open(file_path, 'w', encoding='utf-8') as f:
                f.write(table_content)
            
            file_type = 'table'
            schedule_file = filename
        else:
            return jsonify({'success': False, 'message': '请提供课表文件或表格内容'})
        
        # 保存到数据库
        conn = sqlite3.connect(app.config['DATABASE'])
        cursor = conn.cursor()
        cursor.execute('''
            INSERT INTO schedules (teacher_name, schedule_file, file_type)
            VALUES (?, ?, ?)
        ''', (teacher_name, schedule_file, file_type))
        conn.commit()
        conn.close()
        
        return jsonify({'success': True, 'message': '课表上传成功'})
    
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)})

@app.route('/api/resources')
def get_resources():
    """获取资源列表API"""
    file_type = request.args.get('type', 'all')
    grade = request.args.get('grade', 'all')
    semester = request.args.get('semester', 'all')
    
    conn = sqlite3.connect(app.config['DATABASE'])
    cursor = conn.cursor()
    
    query = "SELECT * FROM resources WHERE 1=1"
    params = []
    
    if file_type != 'all':
        query += " AND file_type = ?"
        params.append(file_type)
    
    if grade != 'all':
        query += " AND grade = ?"
        params.append(grade)
    
    if semester != 'all':
        query += " AND semester = ?"
        params.append(semester)
    
    query += " ORDER BY upload_time DESC"
    
    cursor.execute(query, params)
    resources = cursor.fetchall()
    conn.close()
    
    return jsonify([{
        'id': r[0],
        'filename': r[1],
        'original_name': r[2],
        'file_type': r[3],
        'grade': r[4],
        'semester': r[5],
        'upload_time': r[6],
        'uploader': r[7]
    } for r in resources])

@app.route('/api/schedules')
def get_schedules():
    """获取课表列表API"""
    conn = sqlite3.connect(app.config['DATABASE'])
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM schedules ORDER BY upload_time DESC")
    schedules = cursor.fetchall()
    conn.close()
    
    return jsonify([{
        'id': s[0],
        'teacher_name': s[1],
        'schedule_file': s[2],
        'file_type': s[3],
        'upload_time': s[4]
    } for s in schedules])

@app.route('/download/<file_type>/<filename>')
def download_file(file_type, filename):
    """文件下载"""
    file_path = os.path.join(app.config['UPLOAD_FOLDER'], file_type, filename)
    if os.path.exists(file_path):
        return send_file(file_path, as_attachment=True)
    else:
        return "文件不存在", 404

@app.route('/api/delete_resource/<int:resource_id>', methods=['DELETE'])
def delete_resource(resource_id):
    """删除资源"""
    try:
        conn = sqlite3.connect(app.config['DATABASE'])
        cursor = conn.cursor()
        
        # 获取文件名
        cursor.execute("SELECT filename, file_type FROM resources WHERE id = ?", (resource_id,))
        result = cursor.fetchone()
        
        if result:
            filename, file_type = result
            file_path = os.path.join(app.config['UPLOAD_FOLDER'], file_type, filename)
            
            # 删除文件
            if os.path.exists(file_path):
                os.remove(file_path)
            
            # 删除数据库记录
            cursor.execute("DELETE FROM resources WHERE id = ?", (resource_id,))
            conn.commit()
        
        conn.close()
        return jsonify({'success': True})
    
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)})

if __name__ == '__main__':
    init_db()
    app.run(debug=True, host='0.0.0.0', port=5000)
